package db;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import csv.CSVWriter;

public class DatabaseManager {
	
	private Connection connection;
	
	public DatabaseManager() {
		connection = null;
	}
	
	private void setupConnection() throws SQLException {
		
		if (hasActiveConnection())
			closeConnection();

		try {
			connection = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/vtk", "partyapp",
					"j1smcOi282qHuP");
		} catch (SQLException e) {
			throw new SQLException("Connection Failed!");
		}

		if (connection == null)
			throw new SQLException("Failed to make connection!");
	}
	
	private void closeConnection() {
		try {
			connection.close();
		} catch (SQLException e) { }
		connection = null;
	}
	
	private boolean hasActiveConnection() {
		if (connection == null)
			return false;
		else
			try {
				return !connection.isClosed();
			} catch (SQLException e) {
				return false;
			}
	}

	public void updatePersonTabel(String location) throws SQLException, IOException{
		setupConnection();
		String[] columnNames = new String[] {"userid","voornaam","naam",
				"stub","creationtime","kaartnr","irreeelbijcudi",
				"wisselgegevensalumnivereniging","kulinfoupdate"};
		
		CSVWriter writer = new CSVWriter(new File(location), columnNames);
		
		Statement st;
		st = connection.createStatement();
		ResultSet rs = st.executeQuery("SELECT * FROM student.persoon");
		while (rs.next()) {
			String[] input = new String[columnNames.length];
			for (int i = 0; i <columnNames.length;i++)
				input[i] = rs.getString(columnNames[i]);
			writer.writeToFile(input);
		}
		writer.close();
		closeConnection();
		
	}
	
	public void updateBarcodeTabel(String location) throws SQLException, IOException{
		setupConnection();
		String[] columnNames = new String[] {"code","userid"};
		
		CSVWriter writer = new CSVWriter(new File(location), columnNames);
		
		Statement st;
		st = connection.createStatement();
		ResultSet rs = st.executeQuery("SELECT * FROM student.barcode");
		while (rs.next()) {
			String[] input = new String[columnNames.length];
			for (int i = 0; i <columnNames.length;i++)
				input[i] = rs.getString(columnNames[i]);
			writer.writeToFile(input);
		}
		writer.close();
		closeConnection();
	}
	
	public void updateLidTabel(String location) throws SQLException, IOException{
		setupConnection();
		String[] columnNames = new String[] {"id","jaar", "persoon_userid", "type"};
		
		CSVWriter writer = new CSVWriter(new File(location), columnNames);
		
		Statement st;
		st = connection.createStatement();
		ResultSet rs = st.executeQuery("SELECT * FROM student.lidmaatschap");
		while (rs.next()) {
			String[] input = new String[columnNames.length];
			for (int i = 0; i <columnNames.length;i++)
				input[i] = rs.getString(columnNames[i]);
			writer.writeToFile(input);
		}
		writer.close();
		closeConnection();
	}
	
//	public static void main(String[] args) {
//		DatabaseManager db = new DatabaseManager();
//		try {
//			//db.updatePersonTabel("PersoonTabel.csv");
//			db.updateBarcodeTabel("BarcodeTabel.csv");
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (IOException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	}

}